package org.jeecg.modules.submodules.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.jeecg.modules.submodules.entity.BigOrders;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.jeecg.modules.submodules.entity.vo.*;
import org.springframework.stereotype.Repository;

/**
 * @Description: 订单组
 * @Author: jeecg-boot
 * @Date:   2020-07-17
 * @Version: V1.0
 */
@Repository
public interface BigOrdersMapper extends BaseMapper<BigOrders> {
    @Select("SELECT\n" +
            "\t\n" +
            "\to.ID Id,\n" +
            "\to.ORDERS_NUMBER,\n" +
            "\to.CREATED_BY,\n" +
            "\to.CREATED_TIME,\n" +
            "\to.ORDERS_THEORETICAL_WEIGHT,\n" +
            "\to.ORDERS_ACTUAL_WEIGHT,\n" +
            "\tt.timeId timeId,\n" +
            "\tt.ARRIVE_TIME,\n" +
            "\tt.LOADING_TIME,\n" +
            "\tt.START_TIME,\n" +
            "\tt.UNLOADING_TIME,\n" +
            "\tg.id goodsId,\n" +
            "\tg.GOODS_NAME,\n" +
            "\tstartI.id startId,\n" +
            "\tstartI.depart_name startName,\n" +
            "\tendI.id endId,\n" +
            "\tendI.depart_name endName,\n" +
            "\tcar.id carId,\n" +
            "\tcar.CAR_NUMBER,\n" +
            "\tcar.userId,\n" +
            "\tcar.realname,\n" +
            "\tcar.username \n" +
            "FROM\n" +
            "\tbig_orders bo\n" +
            "\tLEFT JOIN orders_connect oc ON oc.BIG_ORDERS_ID = bo.id\n" +
            "\tLEFT JOIN orders o ON o.id = oc.ORDERS_ID\n" +
            "\tLEFT JOIN (\n" +
            "\tSELECT\n" +
            "\t\to.id," +
            "\nt.id timeId,\n" +
            "\t\tt.LOADING_TIME,\n" +
            "\t\tt.START_TIME,\n" +
            "\t\tt.UNLOADING_TIME,\n" +
            "\t\tt.ARRIVE_TIME \n" +
            "\tFROM\n" +
            "\t\torders o\n" +
            "\t\tLEFT JOIN big_orders_time ot ON ot.ORDERS_ID = o.id\n" +
            "\t\tLEFT JOIN time t ON t.id = ot.TIME_ID\n" +
            "\t) t ON t.id = oc.ORDERS_ID\n" +
            "\tLEFT JOIN (\n" +
            "\tSELECT\n" +
            "\t\tg.id,\n" +
            "\t\tg.GOODS_NAME,\n" +
            "\t\to.id ordersId \n" +
            "\tFROM\n" +
            "\t\tgoods g\n" +
            "\t\tLEFT JOIN orders_goods og ON og.GOODS_ID = g.id\n" +
            "\t\tLEFT JOIN orders o ON o.id = og.ORDERS_ID \n" +
            "\t) g ON g.ordersId = oc.ORDERS_ID\n" +
            "\tLEFT JOIN (\n" +
            "\tSELECT\n" +
            "\t\tsd.id,\n" +
            "\t\tsd.depart_name,\n" +
            "\t\to.id orderId \n" +
            "\tFROM\n" +
            "\t\tsys_depart sd\n" +
            "\t\tLEFT JOIN orders_start os ON os.START_ID = sd.id\n" +
            "\t\tLEFT JOIN orders o ON o.id = os.ORDERS_ID \n" +
            "\t) startI ON startI.orderId = oc.ORDERS_ID\n" +
            "\tLEFT JOIN (\n" +
            "\tSELECT\n" +
            "\t\tsd.id,\n" +
            "\t\tsd.depart_name,\n" +
            "\t\to.id orderId \n" +
            "\tFROM\n" +
            "\t\tsys_depart sd\n" +
            "\t\tLEFT JOIN orders_end oe ON oe.END_ID = sd.id\n" +
            "\t\tLEFT JOIN orders o ON o.id = oe.ORDERS_ID \n" +
            "\t) endI ON endI.orderId = oc.ORDERS_ID\n" +
            "\tLEFT JOIN (\n" +
            "\tSELECT\n" +
            "\t\tcar.id,\n" +
            "\t\tcar.CAR_NUMBER,\n" +
            "\t\tsu.id userId,\n" +
            "\t\tsu.username,\n" +
            "\t\tsu.realname,\n" +
            "\t\tbo.id bigOrdersId \n" +
            "\tFROM\n" +
            "\t\tcar\n" +
            "\t\tLEFT JOIN big_orders_driver bod ON bod.CAR_ID = car.id\n" +
            "\t\tLEFT JOIN sys_user su ON su.id = bod.DRIVER_ID\n" +
            "\t\tLEFT JOIN big_orders bo ON bo.id = bod.BIG_ORDERS_ID \n" +
            "\t) car ON car.bigOrdersID = bo.ID \n" +
            "WHERE\n" +
            "\tbo.id = #{param}\n" +
            "AND o.id is not null\n" +
            "GROUP BY\n" +
            "\to.id")
    List<BigOrdersDetailVo> getOrdersListByBigOrdersId(@Param("param") String id);

    @SelectProvider(type = BigOrdersMapperProvider.class,method = "getBigOrdersPageVoList")
    List<BigOrdersPageVo> getBigOrdersPageVoList(BigOrdersPageVo bigOrdersPageVo);

    @Select("select\n" +
            "o.id id,\n" +
            "sd.id endId\n" +
            "from\n" +
            "orders o \n" +
            "left join orders_end oe on oe.ORDERS_ID = o.id\n" +
            "left join sys_depart sd on sd.id = oe.END_ID\n" +
            "left join orders_connect oc on oc.ORDERS_ID = o.ID\n" +
            "left join big_orders bo on bo.id = oc.BIG_ORDERS_ID\n" +
            "where\n" +
            "bo.id = #{id}\n" +
            "")
    List<GoodsWeightRecordVo> groupEndAndOrdersId(@Param("id") String bigOrdersId);

    @Select("select \n" +
            "t.id\n" +
            "from\n" +
            "orders o\n" +
            "left join orders_end oe on oe.orders_id = o.id\n" +
            "left join big_orders_time bot on bot.orders_id = o.id\n" +
            "left join time t on t.id = bot.TIME_ID\n" +
            "left join orders_connect oc on oc.orders_id = o.id\n" +
            "left join big_orders bo on bo.id = oc.BIG_ORDERS_ID\n" +
            "where oe.END_ID = #{endId} AND bo.id = #{bigOrdersId}")
    String getTimeIDByEndIdAndBiOrdersId(@Param("endId") String endId,@Param("bigOrdersId") String bigOrdersId);

    @SelectProvider(type = BigOrdersMapperProvider.class,method = "getCarReceivingOrders")
    List<CarStatisticsVo> getCarReceivingOrders(CarStatisticsVo vo);


    @SelectProvider(type = BigOrdersMapperProvider.class,method = "getAllGoodsWeight")
    List<GoodsStatisticsVo> getAllGoodsWeight(GoodsStatisticsVo vo);

    @SelectProvider(type = BigOrdersMapperProvider.class,method = "getZhouzhuan")
    String getZhouzhuan();

    @Select("select\n" +
            "g.GOODS_NAME,\n" +
            "IFNULL(CAST(aaa.avgWeight as DECIMAL(10,2)),0) avgWeight\n" +
            "from\n" +
            "goods g \n" +
            "left join (SELECT\n" +
            "\tg.id,\n" +
            "\tsum( o.ORDERS_ACTUAL_WEIGHT ) sss,\n" +
            "\tcounts.counts,\n" +
            "\t( sum( o.ORDERS_ACTUAL_WEIGHT ) / counts.counts ) avgWeight \n" +
            "FROM\n" +
            "\tgoods g\n" +
            "\tLEFT JOIN orders_goods og ON og.goods_Id = g.id\n" +
            "\tLEFT JOIN orders o ON o.id = og.orders_id\n" +
            "\tLEFT JOIN orders_connect oc ON oc.orders_id = o.id\n" +
            "\tLEFT JOIN big_orders bo ON bo.id = oc.BIG_ORDERS_ID\n" +
            "\tLEFT JOIN big_orders_driver bod ON bod.BIG_ORDERS_ID = bo.id\n" +
            "\tLEFT JOIN car c ON c.id = bod.car_id\n" +
            "\tLEFT JOIN (\n" +
            "\tSELECT\n" +
            "\t\tbo.id,\n" +
            "\t\tccc.counts \n" +
            "\tFROM\n" +
            "\t\tbig_orders bo\n" +
            "\t\tLEFT JOIN big_orders_driver bod ON bod.BIG_ORDERS_ID = bo.id\n" +
            "\t\tLEFT JOIN car c ON c.id = bod.car_id\n" +
            "\t\tINNER JOIN (\n" +
            "\t\tSELECT\n" +
            "\t\t\tcount( 1 ) counts \n" +
            "\t\tFROM\n" +
            "\t\t\tbig_orders bo\n" +
            "\t\t\tLEFT JOIN big_orders_driver bod ON bod.BIG_ORDERS_ID = bo.id\n" +
            "\t\t\tLEFT JOIN car c ON c.id = bod.car_id \n" +
            "\t\tWHERE\n" +
            "\t\t\tc.id = #{carId} \n" +
            "\t\t\tAND bo.BIG_ORDERS_STATUS = 6 \n" +
            "\t\t\tAND bo.del_flag = 0 \n" +
            "\t\t) ccc \n" +
            "\tWHERE\n" +
            "\t\tc.id = #{carId} \n" +
            "\t\tAND bo.BIG_ORDERS_STATUS = 6 \n" +
            "\t\tAND bo.del_flag = 0 \n" +
            "\tGROUP BY\n" +
            "\t\tbo.id \n" +
            "\t) counts ON counts.id = bo.id \n" +
            "WHERE\n" +
            "\tbo.BIG_ORDERS_STATUS = 6 \n" +
            "\tAND bo.del_flag = 0 \n" +
            "\tAND c.id = #{carId}\n" +
            "\tAND bo.CREATED_TIME BETWEEN #{A} AND #{B}\n" +
            "GROUP BY\n" +
            "\tg.GOODS_NAME) aaa on aaa.id = g.id")
     List<AvgWeight> getAvgWeight(@Param("carId") String carId,@Param("A") String startDate,@Param("B") String endDate);

    @Select("select\n" +
            "bo.id\n" +
            "from\n" +
            "big_orders bo\n" +
            "left join big_orders_driver bod on bod.BIG_ORDERS_ID = bo.id\n" +
            "left join sys_user su on su.id = bod.DRIVER_ID\n" +
            "where\n" +
            "bo.BIG_ORDERS_STATUS in(2,3,4,5)\n" +
            "AND bo.DEL_FLAG = 0\n" +
            "AND su.id = #{param}\n")
    List<MyExecuteOrdersVo> getMyExecuteOrders(@Param("param") String userId);

    @Select("select\n" +
            "oe.END_ID," +
            "sd.depart_name endName,\n" +
            "GROUP_CONCAT(o.id) ordersIds\n" +
            "from\n" +
            "big_orders bo\n" +
            "left join orders_connect oc on oc.BIG_ORDERS_ID = bo.id\n" +
            "left join orders o on oc.ORDERS_ID = o.id\n" +
            "left join orders_end oe on oe.orders_id = o.id\n" +
            "left join sys_depart sd on sd.id = oe.end_id\n" +
            "left join big_orders_time bot on bot.orders_id = o.id\n" +
            "left join time t on bot.TIME_ID = t.id\n" +
            "where\n" +
            "bo.id = #{bigOrdersId}\n" +
            "AND t.ARRIVE_TIME is null\n" +
            "AND t.UNLOADING_TIME is null\n" +
            "GROUP BY\n" +
            "oe.END_ID")
    List<EndCheckVo> getEndCheckListVo(@Param("bigOrdersId") String bigOrdersId);
    @Select("SELECT\n" +
            "\to.ID Id,\n" +
            "\to.ORDERS_NUMBER,\n" +
            "\to.CREATED_BY,\n" +
            "\to.CREATED_TIME,\n" +
            "\to.ORDERS_THEORETICAL_WEIGHT,\n" +
            "\to.ORDERS_ACTUAL_WEIGHT,\n" +
            "\tt.timeId timeId,\n" +
            "\tt.ARRIVE_TIME,\n" +
            "\tt.LOADING_TIME,\n" +
            "\tt.START_TIME,\n" +
            "\tt.UNLOADING_TIME,\n" +
            "\tg.id goodsId,\n" +
            "\tg.GOODS_NAME,\n" +
            "\tstartI.id startId,\n" +
            "\tstartI.depart_name startName,\n" +
            "\tendI.id endId,\n" +
            "\tendI.depart_name endName,\n" +
            "\tcar.id carId,\n" +
            "\tcar.CAR_NUMBER,\n" +
            "\tcar.userId,\n" +
            "\tcar.realname,\n" +
            "\tcar.username \n" +
            "FROM\n" +
            "\tbig_orders bo\n" +
            "\tLEFT JOIN orders_connect oc ON oc.BIG_ORDERS_ID = bo.id\n" +
            "\tLEFT JOIN orders o ON o.id = oc.ORDERS_ID\n" +
            "\tLEFT JOIN (\n" +
            "\tSELECT\n" +
            "\t\to.id,\n" +
            "\t\tt.id timeId,\n" +
            "\t\tt.LOADING_TIME,\n" +
            "\t\tt.START_TIME,\n" +
            "\t\tt.UNLOADING_TIME,\n" +
            "\t\tt.ARRIVE_TIME \n" +
            "\tFROM\n" +
            "\t\torders o\n" +
            "\t\tLEFT JOIN big_orders_time ot ON ot.ORDERS_ID = o.id\n" +
            "\t\tLEFT JOIN time t ON t.id = ot.TIME_ID \n" +
            "\t) t ON t.id = oc.ORDERS_ID\n" +
            "\tLEFT JOIN (\n" +
            "\tSELECT\n" +
            "\t\tg.id,\n" +
            "\t\tg.GOODS_NAME,\n" +
            "\t\to.id ordersId \n" +
            "\tFROM\n" +
            "\t\tgoods g\n" +
            "\t\tLEFT JOIN orders_goods og ON og.GOODS_ID = g.id\n" +
            "\t\tLEFT JOIN orders o ON o.id = og.ORDERS_ID \n" +
            "\t) g ON g.ordersId = oc.ORDERS_ID\n" +
            "\tLEFT JOIN (\n" +
            "\tSELECT\n" +
            "\t\tsd.id,\n" +
            "\t\tsd.depart_name,\n" +
            "\t\to.id orderId \n" +
            "\tFROM\n" +
            "\t\tsys_depart sd\n" +
            "\t\tLEFT JOIN orders_start os ON os.START_ID = sd.id\n" +
            "\t\tLEFT JOIN orders o ON o.id = os.ORDERS_ID \n" +
            "\t) startI ON startI.orderId = oc.ORDERS_ID\n" +
            "\tLEFT JOIN (\n" +
            "\tSELECT\n" +
            "\t\tsd.id,\n" +
            "\t\tsd.depart_name,\n" +
            "\t\to.id orderId \n" +
            "\tFROM\n" +
            "\t\tsys_depart sd\n" +
            "\t\tLEFT JOIN orders_end oe ON oe.END_ID = sd.id\n" +
            "\t\tLEFT JOIN orders o ON o.id = oe.ORDERS_ID \n" +
            "\t) endI ON endI.orderId = oc.ORDERS_ID\n" +
            "\tLEFT JOIN (\n" +
            "\tSELECT\n" +
            "\t\tcar.id,\n" +
            "\t\tcar.CAR_NUMBER,\n" +
            "\t\tsu.id userId,\n" +
            "\t\tsu.username,\n" +
            "\t\tsu.realname,\n" +
            "\t\tbo.id bigOrdersId \n" +
            "\tFROM\n" +
            "\t\tcar\n" +
            "\t\tLEFT JOIN big_orders_driver bod ON bod.CAR_ID = car.id\n" +
            "\t\tLEFT JOIN sys_user su ON su.id = bod.DRIVER_ID\n" +
            "\t\tLEFT JOIN big_orders bo ON bo.id = bod.BIG_ORDERS_ID \n" +
            "\t) car ON car.bigOrdersID = bo.ID \n" +
            "WHERE\n" +
            "\to.id = #{ordersId}\n" +
            "GROUP BY\n" +
            "\to.id")
    BigOrdersDetailVo getDetailOrdersId(@Param("ordersId") String ordersId);
    /**
     * 运输队长查看自己的订单
     */
    @Select("SELECT\n" +
            "\tbo.id,\n" +
            "\tbo.BIG_ORDERS_NAME,\n" +
            "\tGROUP_CONCAT( o.ORDERS_NUMBER ) ordersNumber,\n" +
            "\tbo.BIG_ORDERS_STATUS,\n" +
            "\to.CREATED_BY,\n" +
            "\to.CREATED_TIME,\n" +
            "\tGROUP_CONCAT( g.GOODS_NAME ) goodsName,\n" +
            "\tstartI.depart_name startName,\n" +
            "\tGROUP_CONCAT( endI.depart_name ) endName,\n" +
            "\tcar.CAR_NUMBER,\n" +
            "\tcar.realname ,\n" +
            "\tbod.CREATED_BY captain\n" +
            "FROM\n" +
            "\tbig_orders bo\n" +
            "\tLEFT JOIN orders_connect oc ON oc.BIG_ORDERS_ID = bo.id\n" +
            "\tLEFT JOIN orders o ON o.id = oc.ORDERS_ID\n" +
            "\tLEFT JOIN (\n" +
            "\tSELECT\n" +
            "\t\tg.id,\n" +
            "\t\tg.GOODS_NAME,\n" +
            "\t\to.id ordersId \n" +
            "\tFROM\n" +
            "\t\tgoods g\n" +
            "\t\tLEFT JOIN orders_goods og ON og.GOODS_ID = g.id\n" +
            "\t\tLEFT JOIN orders o ON o.id = og.ORDERS_ID \n" +
            "\t) g ON g.ordersId = oc.ORDERS_ID\n" +
            "\tLEFT JOIN (\n" +
            "\tSELECT\n" +
            "\t\tsd.id,\n" +
            "\t\tsd.depart_name,\n" +
            "\t\to.id orderId \n" +
            "\tFROM\n" +
            "\t\tsys_depart sd\n" +
            "\t\tLEFT JOIN orders_start os ON os.START_ID = sd.id\n" +
            "\t\tLEFT JOIN orders o ON o.id = os.ORDERS_ID \n" +
            "\t) startI ON startI.orderId = oc.ORDERS_ID\n" +
            "\tLEFT JOIN (\n" +
            "\tSELECT\n" +
            "\t\tcar.id,\n" +
            "\t\tcar.CAR_NUMBER,\n" +
            "\t\tsu.id userId,\n" +
            "\t\tsu.username,\n" +
            "\t\tsu.realname,\n" +
            "\t\tbo.id bigOrdersId \n" +
            "\tFROM\n" +
            "\t\tcar\n" +
            "\t\tLEFT JOIN big_orders_driver bod ON bod.CAR_ID = car.id\n" +
            "\t\tLEFT JOIN sys_user su ON su.id = bod.DRIVER_ID\n" +
            "\t\tLEFT JOIN big_orders bo ON bo.id = bod.BIG_ORDERS_ID \n" +
            "\t) car ON car.bigOrdersID = bo.ID\n" +
            "\tLEFT JOIN (\n" +
            "\tSELECT\n" +
            "\t\tsd.id,\n" +
            "\t\tsd.depart_name,\n" +
            "\t\to.id orderId \n" +
            "\tFROM\n" +
            "\t\tsys_depart sd\n" +
            "\t\tLEFT JOIN orders_end os ON os.END_ID = sd.id\n" +
            "\t\tLEFT JOIN orders o ON o.id = os.ORDERS_ID \n" +
            "\t) endI ON endI.orderId = oc.ORDERS_ID \n" +
            "\tleft join big_orders_driver bod on bod.BIG_ORDERS_ID = bo.id\n" +
            "WHERE\n" +
            "\tbo.BIG_ORDERS_STATUS != 999 \n" +
            "\tAND bod.CREATED_BY = #{username}\n" +
            "GROUP BY\n" +
            "\tbo.ID \n" +
            "ORDER BY\n" +
            "\tbo.CREATED_TIME DESC")
    List<BigOrdersPageVo> captainSearchBigOrders(@Param("username") String username);


    /**
     * Excel表根据时间
     */
    @Select("select\n" +
            "bo.BIG_ORDERS_NAME,\n" +
            "\tbo.BIG_ORDERS_COMMENT,\n" +
            "t.LOADING_TIME carStartTime,\n" +
            "sd.depart_name endName,\n" +
            "o.ORDERS_NUMBER,\n" +
            "g.GOODS_NAME,\n" +
            "c.CAR_NUMBER,\n" +
            "su.realname,\n" +
            "o.ORDERS_THEORETICAL_WEIGHT,\n" +
            "o.ORDERS_ACTUAL_WEIGHT,\n" +
            "t.LOADING_TIME,\n" +
            "t.START_TIME,\n" +
            "t.ARRIVE_TIME,\n" +
            "t.UNLOADING_TIME\t\n" +
            "from\n" +
            "big_orders bo\n" +
            "left join orders_connect oc on oc.BIG_ORDERS_ID = bo.ID\n" +
            "left join orders o on o.id = oc.ORDERS_ID\n" +
            "left join big_orders_time bot on bot.ORDERS_ID = o.id\n" +
            "left join time t on t.id = bot.TIME_ID\n" +
            "left join orders_end oe on oe.ORDERS_ID = o.id\n" +
            "left join sys_depart sd on sd.id = oe.END_ID\n" +
            "left join orders_goods og on og.ORDERS_ID = o.id\n" +
            "left join goods g on g.id = og.GOODS_ID\n" +
            "left join big_orders_driver bod on bod.BIG_ORDERS_ID = bo.id\n" +
            "left join sys_user su on su.id = bod.DRIVER_ID\n" +
            "left join car c on c.id = bod.CAR_ID\n" +
            "where\n" +
            "bo.BIG_ORDERS_STATUS = 6\n" +
            "AND\n" +
            "bo.created_time BETWEEN #{startDate} AND #{endDate}" +
            "\n\tAND bo.del_flag = 0")
    List<BigOrdersExcelVo> getExcelByBigOrders(@Param("startDate") String startDate,@Param("endDate") String endDate);

    /**
     * 生成流水号用
     */
    @Select("SELECT\n" +
            "\tid,\n" +
            "\tbig_orders_name,\n" +
            "\tcreated_by,\n" +
            "\tcreated_time,\n" +
            "\tupdated_by,\n" +
            "\tupdated_time,\n" +
            "\tbig_orders_status,\n" +
            "\tbig_orders_comment ,\n" +
            "\tTO_DAYS(NOW())\n" +
            "FROM\n" +
            "\tbig_orders \n" +
            "WHERE\n" +
            "\tto_days(CREATED_TIME) = to_days(now())\n" +
            "\t")
    List<BigOrders> getNowDayBigOrdersCount();
    /**
     * 查出运输队长的cid
     */
    @Select("select\n" +
            "\n" +
            "su.app_client_id\n" +
            "from \n" +
            "sys_user su\n" +
            "left join sys_user_post sup on sup.USER_ID = su.id\n" +
            "left join sys_position sp on sp.id = sup.POST_ID\n" +
            "where\n" +
            "sp.`name` = '运输队长'\n" +
            "AND \n" +
            "su.app_client_id is not null")
    List<String> getCaptainCid();
}
